1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Data.OleDb;
6 using System.Drawing;
7 using System.IO;
8 using System.Linq;
9 using System.Reflection;
10 using System.Text;
11 using System.Threading.Tasks;
12 using System.Windows.Forms;
13 //using System.Excel;
14 using System.Runtime.InteropServices;
15 using Excel = Microsoft.Office.Interop.Excel;
16
17 namespace SoftQuanLyNhaHang.Views
18 {
19 public partial class frmNhapHang_NhapNhieu : Form
20 {
21 private int _getNhapHangId;
22
23 public frmNhapHang_NhapNhieu()
24 {
25 InitializeComponent();
26 }
27
28 public frmNhapHang_NhapNhieu(int IdNhapHang) : this()
29 {
30 _getNhapHangId = IdNhapHang;
31 }
32 //insert nhap nhanh
33 int _IdNhapNhanh = 0;
34 string _MaSanPham = string.Empty;
35 string _MaVietTat= string.Empty;
36 string _TenSanPham = string.Empty;
37 string _TenDonViTinh= string.Empty;
38 double _SoLuong = 0;
39 double _GiaNhap = 0;
40 double _ChietKhau =0;
41 double _Thue = 0;
42 double _TongTien = 0;
43 //int _NhapHangId = 0;
44 string _TrangThai= "SuDung";
45 DateTime _NgayTao = DateTime.Now;
46
47 int _pageIndex = 1;
48 int _tongSoBanGhi = 0;
49
50 private void btnTaiFileMau_Click(object sender, EventArgs e)
51 {
52 /*
53 var saveFileDialog = new SaveFileDialog();
54 saveFileDialog.DefaultExt = "xls";
55 saveFileDialog.Filter = "Excel files (*.xls)|*.xls |All files (*.*)|*.*";
56 if (saveFileDialog.ShowDialog() == DialogResult.OK)
57 {
58 const string MyFileName = "filename.xls";
59 string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
60 var filePath = Path.Combine(execPath, MyFileName);
61 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
62 Microsoft.Office.Interop.Excel.Workbook book = app.Workbooks.Open(filePath);
63 book.SaveAs(saveFileDialog.FileName); //Save
64 book.Close();
65 }
66 */
67
68 OpenFileDialog op = new OpenFileDialog();
69 op.Filter = "Excel 2007|*.xlsx|Excel 97 - 2003|*.xls";
70 if (op.ShowDialog() == System.Windows.Forms.DialogResult.OK)
71 {
72 if (File.Exists(op.FileName))
73 {
74 string[] Arr = null;
75 Arr = op.FileName.Split('.');
76 if (Arr.Length > 0)
77 {
78 if (Arr[Arr.Length - 1] == "xls")
79 sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
80 op.FileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
81 }
82 else if (Arr[Arr.Length - 1] == "xlsx")
83 {
84 sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + op.FileName + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
85 }
86 }
87
88 }
89
90 }
91
92 public string sConnectionString;
93 /*
94 private void FillData()
95 {
96 if (sConnectionString.Length > 0)
97 {
98 OleDbConnection cn = new OleDbConnection(sConnectionString);
99 {
100 cn.Open();
101 DataTable dt = new DataTable();
102 OleDbDataAdapter Adpt = new OleDbDataAdapter("select * from [sheet1$]", cn);
103 Adpt.Fill(dt);
104 dgvDanhSachNhapHangNhanh.DataSource = dt;
105 }
106
107 }
108 }
109 */
110
111 //lstCustomer
112 //GridLines = true;
113 //--https://www.youtube.com/watch?v=yOdvjUDSj4Y
114 private void btnChonFileNhapHang_Click(object sender, EventArgs e)
115 {
116
117 int rows = 0;
118 int cols = 0;
119
120 OpenFileDialog fopen = new OpenFileDialog();
121 fopen.Filter = "Excel|*.xls;*.xlsx;*.xlsm";
122
123 fopen.ShowDialog();
124
125 int x = 0;
126
127 if(fopen.FileName != "")
128 {
129 //xoa cac rows cu
130 //lstCustomer.Items.Clear();
131 //lstCustomer.Columns.Clear();
132
133 txtPath.Text = fopen.FileName;
134 //tao doi tuong excel
135 Excel.Application app = new Excel.Application();
136 //mo file excel
137 Excel.Workbook wb = app.Workbooks.Open(fopen.FileName);
138 try
139 {
140 //Mở sheet
141 Excel.Worksheet sheet = wb.Sheets[1];
142 Excel.Range range = sheet.UsedRange;
143 //doc du lieu
144 rows = range.Rows.Count;
145 cols = range.Columns.Count;
146
147 //doc dong tieu de de tao cot trong listview
148 /*
149 for (int c = 1; c <= cols; c++)
150 {
151 string columnname = range.Cells[1, c].Value.ToString();
152 ColumnHeader col = new ColumnHeader();
153 col.Text = columnname;
154 col.Width = 120;
155 //lstCustomer.Columns.Add(col);
156 }
157 */
158
159 //Dữ liệu
160 for (int i = 2; i <= rows; i++)
161 {
162 //ListViewItem item = new ListViewItem();
163 for (int j = 1; j <= cols; j++)
164 {
165 /*
166 if (j == 1)
167 {
168 item.Text = range.Cells[i, j].Value.ToString();
169 }
170 else
171 {
172 item.SubItems.Add(range.Cells[i, j].Value.ToString());
173 }
174 */
175 switch (j)
176 {
177 case 1:
178 if (range.Cells[i, j].Value2 != null)
179 {
180 _MaSanPham = range.Cells[i, j].Value.ToString();
181 }
182 else
183 {
184 _MaSanPham = "";
185 }
186
187 break;
188 case 2:
189 if (range.Cells[i, j].Value2 != null)
190 {
191 _MaVietTat = range.Cells[i, j].Value.ToString();
192 }
193 else
194 {
195 _MaVietTat = "";
196 }
197
198 break;
199 case 3:
200 if (range.Cells[i, j].Value2 != null)
201 {
202 _TenSanPham = range.Cells[i, j].Value.ToString();
203 }
204 else
205 {
206 _TenSanPham = "";
207 }
208
209 break;
210 case 4:
211 if (range.Cells[i, j].Value2 != null)
212 {
213 _TenDonViTinh = range.Cells[i, j].Value.ToString();
214 }
215 else
216 {
217 _TenDonViTinh = "";
218 }
219
220 break;
221 case 5:
222 if (range.Cells[i, j].Value2 != null)
223 {
224 _SoLuong = CauHinhHeThong.Format_SoDeTinhToan(range.Cells[i, j].Value.ToString(),false);
225 }
226 else
227 {
228 _SoLuong = 0;
229 }
230
231
232 break;
233 case 6:
234 if (range.Cells[i, j].Value2 != null)
235 {
236 _GiaNhap = CauHinhHeThong.Format_SoDeTinhToan(range.Cells[i, j].Value.ToString(),true);
237 }
238 else
239 {
240 _GiaNhap = 0;
241 }
242
243
244 break;
245 case 7:
246 if (range.Cells[i, j].Value2 != null)
247 {
248 _ChietKhau = CauHinhHeThong.Format_SoDeTinhToan(range.Cells[i, j].Value.ToString(),true);
249 }
250 else
251 {
252 _ChietKhau = 0;
253 }
254
255 break;
256 case 8:
257 if (range.Cells[i, j].Value2 != null)
258 {
259 _Thue = CauHinhHeThong.Format_SoDeTinhToan(range.Cells[i, j].Value.ToString(),true);
260 }
261 else
262 {
263 _Thue = 0;
264 }
265
266
267 break;
268 default:
269 //9
270 double tong = 0;
271 tong = (_GiaNhap * _SoLuong) - (_GiaNhap * _SoLuong * _ChietKhau / 100) + (_GiaNhap * _SoLuong * _Thue / 100);
272 _TongTien = tong;
273 break;
274 }
275 }
276
277 lblSpDangThietLap.Text = _TenSanPham;
278
279 x = Controllers.NhapNhanhCtrl.InsertNhapNhanh(_IdNhapNhanh, _MaSanPham, _MaVietTat, _TenSanPham, _TenDonViTinh, _SoLuong, _GiaNhap, _ChietKhau, _Thue, _TongTien, _getNhapHangId, _TrangThai, _NgayTao);
280 //lblTenSp.Text = _TenSanPham;
281
282 //lstCustomer.Items.Add(item);
283
284 }
285
286 this.HienThiDanhSachSanPham_NhapNhanh_ByIdNhapHang(_getNhapHangId);
287 }
288 catch(Exception ex)
289 {
290 MessageBox.Show(ex.Message, "Thông báo", MessageBoxButtons.OK,MessageBoxIcon.Error);
291 }
292 }
293
294 }
295
296
297 //Hiển thị toàn bộ thông tin sản phẩm trong kho liên quan tới IdNhapHang
298 private void HienThiDanhSachSanPham_NhapNhanh_ByIdNhapHang(int IdNhapHang)
299 {
300 dgvDanhSachNhapHang.Rows.Clear();
301 //Ẩn cột đầu tiên
302 dgvDanhSachNhapHang.RowHeadersVisible = false;
303 //-https://stackoverflow.com/questions/17404969/right-align-a-column-in-datagridview-doesnt-work
304 this.dgvDanhSachNhapHang.Columns["STT"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
305 this.dgvDanhSachNhapHang.Columns["STT"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
306 //this.dgvDanhSachNhapHang.Columns["STT"].SortMode = DataGridViewColumnSortMode.NotSortable;
307
308 this.dgvDanhSachNhapHang.Columns["SoLuong"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
309 this.dgvDanhSachNhapHang.Columns["SoLuong"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
310
311 this.dgvDanhSachNhapHang.Columns["TenDonViTinh"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
312 this.dgvDanhSachNhapHang.Columns["TenDonViTinh"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
313
314 this.dgvDanhSachNhapHang.Columns["GiaNhap"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
315 this.dgvDanhSachNhapHang.Columns["GiaNhap"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
316
317 this.dgvDanhSachNhapHang.Columns["ChietKhau"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
318 this.dgvDanhSachNhapHang.Columns["ChietKhau"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
319
320 this.dgvDanhSachNhapHang.Columns["Thue"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
321 this.dgvDanhSachNhapHang.Columns["Thue"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
322
323
324 this.dgvDanhSachNhapHang.Columns["TongTien"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
325 this.dgvDanhSachNhapHang.Columns["TongTien"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
326
327 DataTable dtNN = new DataTable();
328 //select từ bảng kho
329 //dt = Controllers.KhoCtrl.FillDataSet_getKho_ByNhapHangId(-1, IdNhapHang, 1000, _pageIndex).Tables[0];
330 dtNN = Controllers.NhapNhanhCtrl.FillDataSet_NhapNhanh_Gets_ByNhapHangId(-1, IdNhapHang).Tables[0];
331 if (dtNN.Rows.Count > 0)
332 {
333
334 int _stt = 0;
335
336 //_tongSoBanGhi = Int32.Parse(dtNN.Rows[0]["TongSoBanGhi"].ToString());
337
338 double so_luong = 0;
339 double gia_nhap = 0;
340 double thue = 0;
341 double ckPtram = 0;
342 double tongmotMh = 0;
343
344 foreach (DataRow item in dtNN.Rows)
345 {
346 _stt += 1;
347 int n = dgvDanhSachNhapHang.Rows.Add();
348
349 dgvDanhSachNhapHang.Rows[n].Cells["IdNhapNhanh"].Value = item["IdNhapNhanh"].ToString();
350 dgvDanhSachNhapHang.Rows[n].Cells["MaSanPham"].Value = item["MaSanPham"].ToString();
351 dgvDanhSachNhapHang.Rows[n].Cells["MaVietTat"].Value = item["MaVietTat"].ToString();
352
353 dgvDanhSachNhapHang.Rows[n].Cells["STT"].Value = _stt.ToString();
354 dgvDanhSachNhapHang.Rows[n].Cells["TenSanPham"].Value = item["TenSanPham"].ToString();
355 dgvDanhSachNhapHang.Rows[n].Cells["TenDonViTinh"].Value = item["TenDonViTinh"].ToString();
356
357 so_luong = CauHinhHeThong.Format_SoDeTinhToan(item["SoLuong"].ToString(),false);
358 gia_nhap = CauHinhHeThong.Format_SoDeTinhToan(item["GiaNhap"].ToString(), false);
359 thue = CauHinhHeThong.Format_SoDeTinhToan(item["Thue"].ToString(), false);
360 ckPtram = CauHinhHeThong.Format_SoDeTinhToan(item["ChietKhau"].ToString(), false);
361
362 dgvDanhSachNhapHang.Rows[n].Cells["SoLuong"].Value = CauHinhHeThong.Format_SoDeHienThi(so_luong, false);
363 dgvDanhSachNhapHang.Rows[n].Cells["GiaNhap"].Value = CauHinhHeThong.Format_SoDeHienThi(gia_nhap, true);
364
365 dgvDanhSachNhapHang.Rows[n].Cells["ChietKhau"].Value = CauHinhHeThong.Format_SoDeHienThi(ckPtram, false);
366 dgvDanhSachNhapHang.Rows[n].Cells["Thue"].Value = CauHinhHeThong.Format_SoDeHienThi(thue, false);
367
368 tongmotMh = (gia_nhap * so_luong) - (gia_nhap * so_luong * ckPtram / 100) + (gia_nhap * so_luong * thue / 100);
369
370 dgvDanhSachNhapHang.Rows[n].Cells["TongTien"].Value = CauHinhHeThong.Format_SoDeHienThi(tongmotMh, true);
371 dgvDanhSachNhapHang.Rows[n].Cells["Xoa"].Value = "Xóa";
372 }
373
374 }
375 else
376 {
377 _tongSoBanGhi = 0;
378 }
379
380
381 }
382
383 private void dgvDanhSachNhapHang_CellClick(object sender, DataGridViewCellEventArgs e)
384 {
385 //-https://stackoverflow.com/questions/1132471/how-to-reference-column-name-instead-of-e-columnindex-in-winform-datagridview-ev
386 if (dgvDanhSachNhapHang.Columns[e.ColumnIndex].Name == "Xoa" && e.RowIndex != -1)
387 {
388 //-https://stackoverflow.com/questions/12762036/datagridview-cell-click-event
389 if (dgvDanhSachNhapHang.CurrentCell != null && dgvDanhSachNhapHang.CurrentCell.Value != null)
390 {
391 _IdNhapNhanh = Int32.Parse(dgvDanhSachNhapHang.Rows[e.RowIndex].Cells["IdNhapNhanh"].Value.ToString());
392
393 //hiển thị thông báo xóa chọn yes hoặc no
394 //Controllers.NhapNhanhCtrl.UpdateNhapNhanh_Xoa(_IdNhapNhanh);
395 Controllers.NhapNhanhCtrl.DeleteNhapNhanh(_IdNhapNhanh);
396
397 this.HienThiDanhSachSanPham_NhapNhanh_ByIdNhapHang(_getNhapHangId);
398 }
399 }
400
401
402 }
403
404 private void btnThucHien_Click(object sender, EventArgs e)
405 {
406 //kho_insert_ByNhapNhanh_NhapHang....
407 int i = 0;
408 i = Controllers.KhoCtrl.InsertKho_Select_NhapNhanh_ByNhapHangId(_MaSanPham, _getNhapHangId, _MaVietTat);
409
410 if (i > 0)
411 {
412 //Controllers.NhapNhanhCtrl.UpdateNhapNhanh_Xoa(_IdNhapNhanh);
413 Controllers.NhapNhanhCtrl.DeleteNhapNhanh_ByNhapHangId(0, _getNhapHangId);
414 this.Close();
415 }
416 }
417
418 private void frmNhapHang_NhapNhieu_Load(object sender, EventArgs e)
419 {
420 this.HienThiDanhSachSanPham_NhapNhanh_ByIdNhapHang(_getNhapHangId);
421 }
422
423 }
424 }
